package com.xnew.common.utils;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ooxml.POIXMLDocumentPart;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;

import java.io.*;
import java.util.*;

/**
 * @ClassName ExcelUtil
 * @Description TODO
 * @Author 13503
 * @Date 2019/8/31 20:35
 * @Version 1.0
 **/
public class ExcelUtil {

    private static String tempPath;

    public static String getTempPath(){
        return tempPath;
    }

    public static void setTempPath(String tempPath){
        ExcelUtil.tempPath = tempPath;
    }


    public static Map<Integer, List<Object>> read(String filePath){
        Map<Integer, List<Object>> data = new HashMap<>();
        // 打开指定位置的Excel文件
        FileInputStream file = null;
        try {
            String ext = filePath.substring(filePath.lastIndexOf("."));
            file = new FileInputStream(new File(filePath));
            Workbook workbook = null;
            if(".xls".equals(ext)){
                workbook = new HSSFWorkbook(file);
            }else if(".xlsx".equals(ext)){
                workbook = new XSSFWorkbook(file);
            }else{
                workbook=null;
            }
            // 打开Excel中的第一个Sheet
            Sheet sheet = workbook.getSheetAt(0);
            Map<String, Map<String, PictureData>>  map=null;
            if (filePath.endsWith(".xls")) {
                map = getPictures1((HSSFSheet) sheet);
            } else if(filePath.endsWith(".xlsx")){
                map = getPictures2((XSSFSheet) sheet);
            }
            System.out.println("map:"+map);
            int i = 0;
            for (Row row : sheet) { // 行
                data.put(i, new ArrayList<Object>());
                for (Cell cell : row) { // 单元格
                    switch(cell.getCellType()) {
                        case STRING:
                            if(StringUtils.isNotBlank(cell.getRichStringCellValue().getString())){
                                data.get(i).add(cell.getRichStringCellValue().getString());
                            }

                            break;
                        case NUMERIC:
                            if(DateUtil.isCellDateFormatted(cell)) {
                                data.get(i).add(cell.getDateCellValue());
                            } else {
                                data.get(i).add(cell.getNumericCellValue());
                            }
                            break;
                        case BOOLEAN:
                            data.get(i).add(cell.getBooleanCellValue());
                            break;
                        case FORMULA:
                            data.get(i).add(cell.getCellFormula());
                            break;
                        case BLANK:
//                            data.get(i).add("");
                            break;
                    }
                }
                Map<String, PictureData> rowMap = map.get(""+i);
                if(rowMap!=null){
                    Iterator<Map.Entry<String, PictureData>> entries = rowMap.entrySet().iterator();
                    while(entries.hasNext()){
                        Map.Entry<String, PictureData> entry = entries.next();
                        PictureData value = entry.getValue();
                        data.get(i).add(saveTemp(value));
                    }
                }
                i++;
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

        return data;
    }

    public static Map<Integer, List<String>> readByString(String filePath,Integer length){
        Map<Integer, List<String>> data = new HashMap<>();
        // 打开指定位置的Excel文件
        FileInputStream file = null;
        try {
            String ext = filePath.substring(filePath.lastIndexOf("."));
            file = new FileInputStream(new File(filePath));
            Workbook workbook = null;
            if(".xls".equals(ext)){
                workbook = new HSSFWorkbook(file);
            }else if(".xlsx".equals(ext)){
                workbook = new XSSFWorkbook(file);
            }else{
                workbook=null;
            }
            // 打开Excel中的第一个Sheet
            Sheet sheet = workbook.getSheetAt(0);
            Map<String, Map<String, PictureData>>  map=null;
            if (filePath.endsWith(".xls")) {
                map = getPictures1((HSSFSheet) sheet);
            } else if(filePath.endsWith(".xlsx")){
                map = getPictures2((XSSFSheet) sheet);
            }
            System.out.println("map:"+map);
            int i = 0;
            for (Row row : sheet) { // 行
                data.put(i, new ArrayList<>());
                for (int j = 0; j< length-1;j++) { // 单元格
                    if(row.getCell(j)!=null){
                        row.getCell(j).setCellType(CellType.STRING);
                        data.get(i).add(row.getCell(j).getStringCellValue());
                    }else {
                        data.get(i).add("");
                    }

                }
                Map<String, PictureData> rowMap = map.get(""+i);
                if(rowMap!=null){
                    Iterator<Map.Entry<String, PictureData>> entries = rowMap.entrySet().iterator();
                    while(entries.hasNext()){
                        Map.Entry<String, PictureData> entry = entries.next();
                        PictureData value = entry.getValue();
                        data.get(i).add(saveTemp(value));
                    }
                }
                i++;
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

        return data;
    }

    public static Map<String, Map<String, PictureData>> getPictures1 (HSSFSheet sheet) throws IOException {
        Map<String, Map<String, PictureData>> map = new HashMap<String, Map<String, PictureData>>();
        List<HSSFShape> list = sheet.getDrawingPatriarch().getChildren();
        for (HSSFShape shape : list) {
            if (shape instanceof HSSFPicture) {
                HSSFPicture picture = (HSSFPicture) shape;
                HSSFClientAnchor cAnchor = (HSSFClientAnchor) picture.getAnchor();
                PictureData pdata = picture.getPictureData();
                String key = cAnchor.getRow1() + "-" + cAnchor.getCol1(); // 行号-列号
//                System.out.println("key："+key);
//                map.put(key, pdata);
                Map<String, PictureData> rowMap = map.get(""+cAnchor.getRow1());
                if(rowMap==null){
                    rowMap = new HashMap<>();
                }
                rowMap.put(""+cAnchor.getCol1(),picture.getPictureData());
                map.put(""+cAnchor.getRow1(),rowMap);
            }
        }
        return map;
    }

    /**
     * 获取图片和位置 (xlsx)
     * @param sheet
     * @return
     * @throws IOException
     */
    public static Map<String, Map<String, PictureData>> getPictures2 (XSSFSheet sheet) throws IOException {
        Map<String, Map<String, PictureData>> map = new HashMap<String, Map<String, PictureData>>();
        List<POIXMLDocumentPart> list = sheet.getRelations();
        for (POIXMLDocumentPart part : list) {
            if (part instanceof XSSFDrawing) {
                XSSFDrawing drawing = (XSSFDrawing) part;
                List<XSSFShape> shapes = drawing.getShapes();
                for (XSSFShape shape : shapes) {
                    XSSFPicture picture = (XSSFPicture) shape;
                    XSSFClientAnchor anchor = picture.getPreferredSize();
                    CTMarker marker = anchor.getFrom();
//                    String key = marker.getRow() + "-" + marker.getCol();
                    Map<String, PictureData> rowMap = map.get(marker.getRow());
                    if(rowMap==null){
                        rowMap = new HashMap<>();
                    }
                    rowMap.put(""+marker.getCol(),picture.getPictureData());
                    map.put(""+marker.getRow(),rowMap);
                }
            }
        }
        return map;
    }

    public static String saveTemp(PictureData pic)throws IOException{
        String ext = pic.suggestFileExtension();
        String fileName = FileUtils.renameByExt(ext);
        String imgPath=FileUtils.createDir(tempPath)+fileName;
        byte[] data = pic.getData();
        FileOutputStream out = new FileOutputStream(imgPath);
        out.write(data);
        out.close();
        return imgPath;
    }

    public static void printImg(Map<String, PictureData> sheetList) throws IOException {

        //for (Map<String, PictureData> map : sheetList) {
        Object key[] = sheetList.keySet().toArray();
        for (int i = 0; i < sheetList.size(); i++) {
            // 获取图片流
            PictureData pic = sheetList.get(key[i]);
            // 获取图片索引
            String picName = key[i].toString();
            // 获取图片格式
            String ext = pic.suggestFileExtension();

            byte[] data = pic.getData();

            //图片保存路径
            FileOutputStream out = new FileOutputStream("D:\\img\\pic" + picName + "." + ext);
            out.write(data);
            out.close();
        }
        // }

    }
}
